<!---
	Title:		Elders Program Manager
	Author:		Bruce Frick
	Purpose:	Reports functions for the software
--->
<cfcomponent extends="queries">

	<!--- Get Intake Signatures--->
    <cffunction name="funcQrySelectIntakesForSignature" returntype="any">
    	<cfargument name="Type" default="ElderList" required="no" />

        <cfquery datasource="eldersprogram" name="qrySelectIntakesForSignature">
			SELECT [ElderSignature], [StaffSignature], [SupervisorSignature],
            	[ElderSignatureDate], [StaffSignatureDate], [SupervisorSignatureDate], [ElderListID],
                [LastName], [FirstName]
            FROM [#ARGUMENTS.Type#]
            WHERE [SupervisorSignature] IS NULL
        </cfquery>
        
    	<cfreturn qrySelectIntakesForSignature />
    </cffunction>
    <!--- Get Intake Signatures--->


 
    <!--- Get Master Activity Report--->
    <cffunction name="funcQryMaterActiviyReportAll" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />
        <cfquery datasource="eldersprogram" name="qryMaterActiviyReport">
        SELECT 
            SUM(1 * Chore) AS Chore, 
            SUM(1 * DayOfCare) AS DayOfCare, 
            SUM(1 * MedicalTransportation) AS MedicalTransportation,
        	SUM(1 * InHomeVisit) AS InHomeVisit, 
        	SUM(1 * GroupVisit) AS GroupVisit,
            SUM(1 * TTCM) AS TTCM, 
            SUM(1 * OffSiteVisit) AS OffSiteVisit,
            SUM(1 * FamilySupport) AS FamilySupport, 
            SUM(1 * InformationFCSI) AS InformationFCSI,
            SUM(1 * AssistanceFCSI) AS AssistanceFCSI, 
            SUM(1 * Escort) AS Escort,
            SUM(1 * FoodAssistance) AS FoodAssistance, 
            SUM(1 * FoodBox) AS FoodBox,
            SUM(1 * HealthPromotions) AS HealthPromotions, 
            SUM(1 * Information) AS Information,
            SUM(1 * InPatientVisit) AS InPatientVisit, 
            SUM(1 * Interpretation) AS Interpretation,
            SUM(1 * MedRefillAssistance) AS MedRefillAssistance, 
            SUM(1 * ReferralService) AS ReferralService,
            SUM(TelephoningCount) AS Telephoning, 
            SUM(1 * WritingReading) AS WritingReading,
            SUM(ActivitiesOuting) AS ActivitiesOuting, 
            SUM(Banking) AS Banking,
            SUM(MedicationPickUp) AS MedicationPickUp, 
            SUM(OtherTransport) AS OtherTransport,
            SUM(Potluck) AS Potluck, SUM(Shopping) AS Shopping, 
            SUM(1 * DNKATransportation) AS DNKATransportation,
            SUM(1 * DNKAHomeVisit) AS DNKAHomeVisit, 
            SUM(1 * DNKAGroupVisit) AS DNKAGroupVisit,
            SUM(1 * CancelledTransportation) AS CancelledTransportation, 
            SUM(1 * CancelledHomeVisit) AS CancelledHomeVisit,
            SUM(1 * CancelledGroupVisit) AS CancelledGroupVisit
        FROM DailyProgressNotes
        WHERE NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND 
            <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.ElderID NOT IN (
                    SELECT elderList.elderListID
                    FROM ElderList 
                    WHERE ElderList.PriorityID = 6 OR ElderList.PriorityID = 8                        
                )
        </cfquery>
    	<cfreturn qryMaterActiviyReport />
    </cffunction>
    <!--- Get Master Activity Report--->



    <!--- Get ProviderDistribution Report--->
    <cffunction name="funcQryProviderDistribution" returntype="any">
        <cfset BASE = CreateObject("component", "cfc.base") />
        <cfset Providers = BASE.SCF_Exchange_GetEmployeesInDL("CN=SCF Elders Program,OU=Dynamic Groups,OU=SCFUsers,DC=SouthcentralFoundation,DC=com") />
		<cfset prov_list = "" />
		<cfloop from="1" to="#ArrayLen(Providers)#" index="i" >
		  <cfset prov_list &= "#Providers[i].RosettaID#," />
		</cfloop>    	
        <cfquery datasource="eldersprogram" name="qryProviderDistribution">
			SELECT 
			    COUNT(ElderList.ElderListID) AS Count, 
			    ElderList.PriorityID AS PriorityID, Priority.PriorityName, ElderList.ProviderID AS ProviderID
			FROM
			    Priority INNER JOIN
			    ElderList ON ElderList.PriorityID = Priority.PriorityID
            WHERE ElderList.ProviderID IN (#left( prov_list, len(prov_list)-1)#)
			GROUP BY 
			    ElderList.PriorityID, Priority.PriorityName, ElderList.ProviderID
			ORDER BY 
			    ProviderID, Priority.PriorityName
        </cfquery>
    	<cfreturn qryProviderDistribution />
    </cffunction>
    <!--- Get ProviderDistribution Report--->



    <!--- Get Undup Services Report--->
    <cffunction name="funcQryUnDupServices" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryServicesByElderAndDate" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT *
            FROM ServicesByElderAndDate
            WHERE NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
        </cfquery>

        <cfquery dbtype="query" name="qryUndupServices1">
            SELECT qryServicesByElderAndDate.ElderID, SUM(qryServicesByElderAndDate.ServiceCount) AS SumOfServiceCount
            FROM qryServicesByElderAndDate
            GROUP BY ElderID, NoteDate
        </cfquery>

        <cfquery dbtype="query" name="qryUndupService2">
            SELECT qryUndupServices1.ElderID, SUM(qryUndupServices1.SumOfServiceCount) AS SumOfServiceCount
            FROM qryUndupServices1
            GROUP BY ElderID
        </cfquery>

        <cfquery dbtype="query" name="qryUndupService3">
            SELECT COUNT(qryUndupService2.ElderID) AS CountOfElderID,
            	SUM(qryUndupService2.SumOfServiceCount) AS SumOfServiceCounts
            FROM qryUndupService2
        </cfquery>

    	<cfreturn qryUndupService3 />
    </cffunction>
    <!--- Get Undup Services Report--->



    <!--- Get Undup Services Report--->
    <cffunction name="funcQryUnDupMeals" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryUndupMealCount1" >
            SELECT ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS Name,
            	COUNT(CongregateMeals.MealID)
            AS MealCount
            FROM ElderList INNER JOIN
            CongregateMeals ON ElderList.ElderListID = CongregateMeals.ElderID
            WHERE MealDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
            GROUP BY ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName
        </cfquery>

        <cfquery dbtype="query" name="qryUndupMealCount2">
            SELECT COUNT(ElderListID) AS CountOfElderID, SUM(MealCount) AS SumOfMealCount
            FROM qryUndupMealCount1
        </cfquery>

    	<cfreturn qryUndupMealCount2 />
    </cffunction>
    <!--- Get Undup Services Report--->



    <!--- Get Undup Services Report--->
    <cffunction name="funcQryUnDupServiceOrMeals" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryServicesByElderAndDate" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT *
            FROM ServicesByElderAndDate
            WHERE NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
        </cfquery>

        <cfquery dbtype="query" name="qryUndupServices1">
            SELECT ElderID AS ElderListID, SUM(ServiceCount) AS SumOfServiceCount
            FROM qryServicesByElderAndDate
            GROUP BY ElderID, NoteDate
        </cfquery>

        <cfquery datasource="eldersprogram" name="qryUndupMealCount1" >
            SELECT ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS Name,
            	COUNT(CongregateMeals.MealID) AS MealCount
            FROM ElderList INNER JOIN
            CongregateMeals ON ElderList.ElderListID = CongregateMeals.ElderID
            WHERE MealDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
            GROUP BY ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName
        </cfquery>

        <cfquery dbtype="query" name="qryUndupServiceOrMeal1">
            SELECT ElderListID, SUM(MealCount) AS ServiceOrMeal
            FROM qryUndupMealCount1
            GROUP BY ElderListID
            UNION ALL
            SELECT ElderListID, SUM(SumOfServiceCount) AS ServiceOrMeal
            FROM qryUndupServices1
            GROUP BY ElderListID
        </cfquery>

       <cfquery dbtype="query" name="qryUndupServiceOrMeal2">
            SELECT ElderListID, ServiceOrMeal AS SumOfServiceOrMeal
            FROM qryUndupServiceOrMeal1
        </cfquery>

         <cfquery dbtype="query" name="qryUndupServiceOrMeal3">
            SELECT COUNT(ElderListID) AS CountOfID, SUM(SumOfServiceOrMeal) AS SumOfServiceOrMeal
            FROM qryUndupServiceOrMeal2
        </cfquery>

    	<cfreturn qryUndupServiceOrMeal3 />
    </cffunction>
    <!--- Get Undup Services Report--->



    <!--- Get Billable Services Report--->
    <cffunction name="funcQryBillableServices" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryBillableServices" >
            SELECT
                ElderList.LastName + ', ' + ElderList.FirstName AS Name, ElderList.ElderListID, 'Chore' AS Service,
                SUM
                (
                    (ISNULL( (DateDiff("n",DailyProgressNotes.ChoreTimeIn, DailyProgressNotes.ChoreTimeOut) / 15), 0)) +
                    (ISNULL( (DateDiff("n",DailyProgressNotes.DayOfCareTimeIn, DailyProgressNotes.DayOfCareTimeOut) / 15 ), 0))
                ) AS TotalUnits,
                ISNULL(SUM(DailyProgressNotes.UnbilledChoreUnits), 0) + ISNULL(SUM(DailyProgressNotes.UnbilledDayOfCareUnits), 0) AS UnbilledUnits
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                (DailyProgressNotes.Chore > 0 OR DailyProgressNotes.DayOfCare > 0) AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.LastName + ', ' + ElderList.FirstName, ElderList.ElderListID

            UNION ALL

            SELECT
                ElderList.LastName + ', ' + ElderList.FirstName AS Name, ElderList.ElderListID, 'Transportation' AS Service,
                ISNULL(SUM (DailyProgressNotes.MedicalTransportationTrips), 0) +
			    ISNULL(SUM (DailyProgressNotes.Shopping), 0) + 
			    ISNULL(SUM (DailyProgressNotes.Banking), 0) + 
			    ISNULL(SUM (DailyProgressNotes.Potluck), 0) +
			    ISNULL(SUM (DailyProgressNotes.OtherTransport), 0) + 
			    ISNULL(SUM (DailyProgressNotes.MedicationPickUp), 0) +  
			    ISNULL(SUM (DailyProgressNotes.ActivitiesOuting), 0) AS TotalUnits,
                ISNULL(SUM (DailyProgressNotes.UnbilledMedicalTransportationUnits), 0) AS UnbilledUnits
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                (
			        DailyProgressNotes.MedicalTransportationTrips > 0 OR
			        DailyProgressNotes.Shopping > 0 OR
			        DailyProgressNotes.Banking > 0 OR 
			        DailyProgressNotes.Potluck > 0 OR
			        DailyProgressNotes.OtherTransport > 0 OR
			        DailyProgressNotes.MedicationPickUp > 0 OR
			        DailyProgressNotes.ActivitiesOuting > 0
			    ) AND                
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.LastName + ', ' + ElderList.FirstName, ElderList.ElderListID

            UNION ALL

            SELECT
                ElderList.LastName + ', ' + ElderList.FirstName AS Name, ElderList.ElderListID, 'TTCM' AS Service,
                SUM
                (
                    (ISNULL( (DateDiff("n",DailyProgressNotes.TTCMTimeIn, DailyProgressNotes.TTCMTimeOut) / 15), 0))
                ) AS TotalUnits,
                ISNULL(SUM(DailyProgressNotes.UnbilledTTCMUnits), 0) AS UnbilledUnits
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                (DailyProgressNotes.TTCM > 0) AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.LastName + ', ' + ElderList.FirstName, ElderList.ElderListID

            UNION ALL

            SELECT
                ElderList.LastName + ', ' + ElderList.FirstName AS Name, ElderList.ElderListID, 'Meals' AS Service,
                COUNT(CongregateMeals.MealID) AS TotalUnits,
                COUNT(CongregateMeals.MealID) AS UnbilledUnits
            FROM
                ElderList INNER JOIN
                CongregateMeals ON ElderList.ElderListID = CongregateMeals.ElderID
            WHERE
                CongregateMeals.MealDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />  AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.LastName + ', ' + ElderList.FirstName, ElderList.ElderListID

            ORDER BY
                ElderList.LastName + ', ' + ElderList.FirstName
        </cfquery>

    	<cfreturn qryBillableServices />
    </cffunction>
    <!--- Get Billable Services Report--->



    <cffunction name="funcQryServicePlansTargets" returntype="any" >
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />
        <cfargument default="-1" name="ProviderID" />

        <cfquery datasource="eldersprogram" name="qryServicePlansByElder" >
            SELECT
                ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, MAX(ServicePlans.PlanDate) AS PlanDate,
                SUM(ServicePlans.MealPreparation) AS MealPrep, SUM(ServicePlans.LightChore) AS LightChore, SUM(ServicePlans.HomeVisit) AS HomeVist,
                SUM(ServicePlans.GroupVisit) AS GroupVisit, SUM(ServicePlans.Telephoning) AS Telephoning, COUNT(ServicePlans.PlanID) AS ServicePlans
            FROM
            	ServicePlans INNER JOIN
            		ElderList ON ServicePlans.ElderID = ElderList.ElderListID
            WHERE
            	ServicePlans.PlanDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
                <cfif ARGUMENTS.ProviderID NEQ -1>
                	AND ElderList.ProviderID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.ProviderID#" />
                </cfif> AND
                ElderList.PriorityID <> 6 or ElderList.PriorityID <> 8
            GROUP BY
            	ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone
        </cfquery>

    	<cfreturn qryServicePlansByElder />
    </cffunction>




    <cffunction name="funcQryDPNTargets" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />
		<cfargument default="-1" name="ProviderID" />

		<cfquery datasource="eldersprogram" name="qryDPNByElder">
            SELECT
                ElderList.ElderListID, DailyProgressNotes.TTCMType,
                SUM(DailyProgressNotes.ActivitiesOuting) AS ActivitiesOuting,
                SUM(DailyProgressNotes.Banking) AS Banking,
                SUM(DailyProgressNotes.MedicationPickUp) AS MedicationPickUp,
                SUM(DailyProgressNotes.OtherTransport) AS OtherTransport,
                SUM(DailyProgressNotes.Potluck) AS Potluck,
                SUM(DailyProgressNotes.Shopping) AS Shopping,
                SUM(DailyProgressNotes.MedicalTransportationTrips) AS MedicalTransportationTrips,
                SUM(DailyProgressNotes.TelephoningCount) AS TelephoningCount,
                ISNULL( SUM(1 * DailyProgressNotes.Chore), 0) AS Chore,
                ISNULL( SUM(1 * DailyProgressNotes.DayOfCare), 0) AS DayOfCare,
                ISNULL( SUM(1 * DailyProgressNotes.MedicalTransportation), 0) AS MedicalTransportation,
                ISNULL( SUM(1 * DailyProgressNotes.InHomeVisit), 0) AS InHomeVisit,
                ISNULL( SUM(1 * DailyProgressNotes.GroupVisit), 0) AS GroupVisit,
                ISNULL( SUM(1 * DailyProgressNotes.OffSiteVisit), 0) AS OffSiteVisit,
                ISNULL( SUM(1 * DailyProgressNotes.FamilySupport), 0) AS FamilySupport,
                ISNULL( SUM(1 * DailyProgressNotes.InformationFCSI), 0) AS InformationFCSI,
                ISNULL( SUM(1 * DailyProgressNotes.AssistanceFCSI), 0) AS AssistanceFCSI,
                ISNULL( SUM(1 * DailyProgressNotes.Escort), 0) AS Escort,
                ISNULL( SUM(1 * DailyProgressNotes.LightMealPrep), 0) AS LightMealPrep,
                ISNULL( SUM(1 * DailyProgressNotes.FoodAssistance), 0) AS FoodAssistance,
                ISNULL( SUM(1 * DailyProgressNotes.FoodBox), 0) AS FoodBox,
                ISNULL( SUM(1 * DailyProgressNotes.HealthPromotions), 0) AS HealthPromotions,
                ISNULL( SUM(1 * DailyProgressNotes.Information), 0) AS Information,
                ISNULL( SUM(1 * DailyProgressNotes.InPatientVisit), 0) AS InPatientVisit,
                ISNULL( SUM(1 * DailyProgressNotes.Interpretation), 0) AS Interpretation,
                ISNULL( SUM(1 * DailyProgressNotes.MedRefillAssistance), 0) AS MedRefillAssistance,
                ISNULL( SUM(1 * DailyProgressNotes.ReferralService), 0) AS ReferralService,
                ISNULL( SUM(1 * DailyProgressNotes.Telephoning), 0) AS Telephoning,
                ISNULL( SUM(1 * DailyProgressNotes.WritingReading), 0) AS WritingReading,
                ISNULL( SUM(1 * DailyProgressNotes.TTCM), 0) AS TTCM,
                ISNULL( SUM(1 * DailyProgressNotes.DNKATransportation), 0) AS DNKATransportation,
                ISNULL( SUM(1 * DailyProgressNotes.DNKAHomeVisit), 0) AS DNKAHomeVisit,
                ISNULL( SUM(1 * DailyProgressNotes.DNKAGroupVisit), 0) AS DNKAGroupVisit,
                ISNULL( SUM(1 * DailyProgressNotes.DNKATelephoning), 0) AS DNKATelephoning,
                ISNULL( SUM(1 * DailyProgressNotes.DNKAChore), 0) AS DNKAChore,
                ISNULL( SUM(1 * DailyProgressNotes.DNKAMealPrep), 0) AS DNKAMealPrep,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledTransportation), 0) AS CancelledTransportation,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledHomeVisit), 0) AS CancelledHomeVisit,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledGroupVisit), 0) AS CancelledGroupVisit,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledTelephoning), 0) AS CancelledTelephoning,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledChore), 0) AS CancelledChore,
                ISNULL( SUM(1 * DailyProgressNotes.CancelledMealPrep), 0) AS CancelledMealPrep

            FROM
            	ElderList AS ElderList INNER JOIN
            	DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID
            WHERE
            	DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
                <cfif ARGUMENTS.ProviderID NEQ -1>
                	AND DailyProgressNotes.ProviderID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.ProviderID#" />
                </cfif>
                AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
            	ElderList.ElderListID, DailyProgressNotes.TTCMType
        </cfquery>

    	<cfreturn qryDPNByElder />
    </cffunction>



    <cffunction name="funcQryMedicaidBillingTransportation" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

		<cfquery datasource="eldersprogram" name="qryMedicaidBillingTransportation">
            SELECT
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS CustomerName, ElderList.DOB,
                ElderList.SignatureCaseNumber, DailyProgressNotes.MedicalTransportation AS Service, PriorAuthorizations.AuthNum,
                DailyProgressNotes.ProviderID, Provider.ProviderLastName + ', ' + Provider.ProviderFirstName AS ProviderName,
                DailyProgressNotes.MedicalTransportationTrips AS TotalUnits, DailyProgressNotes.NoteDate
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID INNER JOIN
                PriorAuthorizations ON DailyProgressNotes.ElderID = PriorAuthorizations.ElderID LEFT OUTER JOIN
                Provider ON DailyProgressNotes.ProviderID = Provider.ProviderID
            WHERE
       			DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
            	DailyProgressNotes.MedicalTransportationTrips > 0 AND
                PriorAuthorizations.ServiceAuthorized = 'Transportation' AND
               ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
       </cfquery>

    	<cfreturn qryMedicaidBillingTransportation />
    </cffunction>



    <cffunction name="funcQryMedicaidBillingChore" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

       <cfquery datasource="eldersprogram" name="qryMedicaidBillingChore">
            SELECT
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS CustomerName, ElderList.DOB,
                ElderList.SignatureCaseNumber, DailyProgressNotes.Chore AS Service,
                DailyProgressNotes.ProviderID, Provider.ProviderLastName + ', ' + Provider.ProviderFirstName AS ProviderName,
                SUM(1 * DailyProgressNotes.Chore) AS TotalUnits, DailyProgressNotes.NoteDate
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID INNER JOIN
                Provider ON DailyProgressNotes.ProviderID = Provider.ProviderID
            WHERE
       			DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
            	DailyProgressNotes.Chore > 0 AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName,
                ElderList.DOB, ElderList.SignatureCaseNumber, DailyProgressNotes.Chore,
                DailyProgressNotes.ProviderID,
                Provider.ProviderLastName + ', ' + Provider.ProviderFirstName,
                DailyProgressNotes.NoteDate
       </cfquery>

    	<cfreturn qryMedicaidBillingChore />
    </cffunction>



    <cffunction name="funcQryMedicaidBillingMeal" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

       <cfquery datasource="eldersprogram" name="qryMedicaidBillingMeal">
            SELECT
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS CustomerName, ElderList.DOB,
                ElderList.SignatureCaseNumber, DailyProgressNotes.FoodBox AS Service, PriorAuthorizations.AuthNum,
                DailyProgressNotes.ProviderID, Provider.ProviderLastName + ', ' + Provider.ProviderFirstName AS ProviderName,
                SUM(1 * DailyProgressNotes.FoodBox) AS TotalUnits, DailyProgressNotes.NoteDate
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID INNER JOIN
                PriorAuthorizations ON DailyProgressNotes.ElderID = PriorAuthorizations.ElderID LEFT OUTER JOIN
                Provider ON DailyProgressNotes.ProviderID = Provider.ProviderID
            WHERE
       			DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
            	DailyProgressNotes.FoodBox > 0 AND
                PriorAuthorizations.ServiceAuthorized = 'Meal'AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName,
                ElderList.DOB, ElderList.SignatureCaseNumber, DailyProgressNotes.FoodBox,
                PriorAuthorizations.AuthNum, DailyProgressNotes.ProviderID,
                Provider.ProviderLastName + ', ' + Provider.ProviderFirstName,
                DailyProgressNotes.NoteDate
       </cfquery>

    	<cfreturn qryMedicaidBillingMeal />
    </cffunction>



    <cffunction name="funcQryMedicaidBillingTTCM" returntype="any">
    	<cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryMedicaidBillingTTCM">
            SELECT
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName AS CustomerName, ElderList.DOB,
                ElderList.SignatureCaseNumber, DailyProgressNotes.TTCM AS Service, PriorAuthorizations.AuthNum,
                DailyProgressNotes.ProviderID, Provider.ProviderLastName + ', ' + Provider.ProviderFirstName AS ProviderName,
                SUM(1 * DailyProgressNotes.TTCM) AS TotalUnits, DailyProgressNotes.NoteDate
            FROM
                ElderList INNER JOIN
                DailyProgressNotes ON ElderList.ElderListID = DailyProgressNotes.ElderID INNER JOIN
                PriorAuthorizations ON DailyProgressNotes.ElderID = PriorAuthorizations.ElderID LEFT OUTER JOIN
                Provider ON DailyProgressNotes.ProviderID = Provider.ProviderID
            WHERE
       			DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> AND <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
            	DailyProgressNotes.TTCM > 0 AND
                PriorAuthorizations.ServiceAuthorized = 'TTCM' AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName + ', ' + ElderList.FirstName,
                ElderList.DOB, ElderList.SignatureCaseNumber, DailyProgressNotes.TTCM,
                PriorAuthorizations.AuthNum, DailyProgressNotes.ProviderID,
                Provider.ProviderLastName + ', ' + Provider.ProviderFirstName,
                DailyProgressNotes.NoteDate
        </cfquery>

    	<cfreturn qryMedicaidBillingTTCM />
    </cffunction>



    <cffunction name="funcQryFCSIServicesSummary" returntype="any">
        <cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />

        <cfquery datasource="eldersprogram" name="qryFCSIServicesSummary">
        <!---Uses a view FCSIServiceDetails--->
        SELECT
            ElderList.LastName + ', ' + ElderList.FirstName AS ElderName,
            FCSIServiceDetails.Service, Sum(FCSIServiceDetails.ServiceCount) AS SumOfServiceCount
        FROM
            ElderList INNER JOIN
            FCSIServiceDetails ON ElderList.ElderListID = FCSIServiceDetails.ElderID
        WHERE
            FCSIServiceDetails.NoteDate Between <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />
        GROUP BY
            ElderList.LastName + ', ' + ElderList.FirstName, FCSIServiceDetails.Service
        </cfquery>

        <cfreturn qryFCSIServicesSummary />
    </cffunction>



    <cffunction name="funcQryBillingReport" returntype="any">
        <cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />
        <cfargument default="-1" name="ElderID" />

        <cfset retVal = {} />

        <cfquery datasource="eldersprogram" name="qryBillingReport" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Chore' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                SUM
                (
                    (ISNULL( (DateDiff("n",DailyProgressNotes.ChoreTimeIn, DailyProgressNotes.ChoreTimeOut) / 15), 0)) +
                    (ISNULL( (DateDiff("n",DailyProgressNotes.DayOfCareTimeIn, DailyProgressNotes.DayOfCareTimeOut) / 15 ), 0))
                ) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                (DailyProgressNotes.Chore > 0 OR DailyProgressNotes.DayOfCare > 0) AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
                
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL
            
            SELECT
			    ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'TTCM' AS ServiceType,
			    MAX(DailyProgressNotes.NoteDate) AS DateOfService, MAX(DailyProgressNotes.DailyProgressNoteID) AS ServiceID,
			    MAX(DailyProgressNotes.ProviderID) AS ProviderID, 1 AS TotalUnits
			FROM
			    DailyProgressNotes INNER JOIN
			    ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
			WHERE
			    DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
			    DailyProgressNotes.TTCM > 0 AND
			    DailyProgressNotes.ElderID IN (
			    
			        SELECT DailyProgressNotes.ElderID
			        FROM DailyProgressNotes
			        WHERE DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                    <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
			            DailyProgressNotes.TTCM > 0 
			        GROUP BY DailyProgressNotes.ElderID
			        HAVING COUNT(DailyProgressNotes.ElderID) >= 3   
			    ) AND
               ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
			     
			GROUP BY
			    ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Meals' AS ServiceType,
                CongregateMeals.MealDate AS DateOfService, CongregateMeals.MealID AS ServiceID,
                CongregateMeals.ProviderID,
                COUNT(CongregateMeals.MealID) AS TotalUnits
            FROM
                CongregateMeals INNER JOIN
                ElderList ON ElderList.ElderListID = CongregateMeals.ElderID
            WHERE
                CongregateMeals.MealDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                CongregateMeals.ProviderID,
                CongregateMeals.MealDate, CongregateMeals.MealID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Transportation' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,                                
                ISNULL( (
			        SUM(DailyProgressNotes.MedicalTransportationTrips) + 
			        SUM(DailyProgressNotes.ActivitiesOuting) + 
			        SUM(DailyProgressNotes.OtherTransport) + 
			        SUM(DailyProgressNotes.Potluck) + 
			        SUM(DailyProgressNotes.MedicationPickUp) + 
			        SUM(DailyProgressNotes.Banking)
			    ), 0 ) AS TotalUnits
                
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                ( 
			        DailyProgressNotes.MedicalTransportationTrips > 0 OR
			        DailyProgressNotes.ActivitiesOuting > 0 OR 
			        DailyProgressNotes.OtherTransport > 0 OR
			        DailyProgressNotes.Potluck > 0 OR
			        DailyProgressNotes.Banking > 0 OR
			        DailyProgressNotes.MedicationPickUp > 0 
			    ) AND
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            ORDER BY
                ElderList.LastName

        </cfquery>

        <Cfset retVal.BilingReport = qryBillingReport />


        <cfreturn qryBillingReport />
    </cffunction>



    <cffunction name="funcQryServicesReport" returntype="any">
        <cfargument default="#now()#" name="DateFrom" />
        <cfargument default="#now()#" name="DateTo" />
        <cfargument default="-1" name="ElderID" />

        <cfset retVal = {} />

        <cfquery datasource="eldersprogram" name="qryServicesReport" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Chore' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                SUM
                (
                    (ISNULL( (DateDiff("n",DailyProgressNotes.ChoreTimeIn, DailyProgressNotes.ChoreTimeOut) / 15), 0)) +
                    (ISNULL( (DateDiff("n",DailyProgressNotes.DayOfCareTimeIn, DailyProgressNotes.DayOfCareTimeOut) / 15 ), 0))
                ) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                (DailyProgressNotes.Chore > 0 OR DailyProgressNotes.DayOfCare > 0) And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'TTCM' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                SUM( DateDiff("n",DailyProgressNotes.TTCMTimeIn, DailyProgressNotes.TTCMTimeOut) / 15 ) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.TTCM > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Meals' AS ServiceType,
                CongregateMeals.MealDate AS DateOfService, CongregateMeals.MealID AS ServiceID,
                CongregateMeals.ProviderID,
                COUNT(CongregateMeals.MealID) AS TotalUnits
            FROM
                CongregateMeals INNER JOIN
                ElderList ON ElderList.ElderListID = CongregateMeals.ElderID
            WHERE
                CongregateMeals.MealDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" />  And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                CongregateMeals.ProviderID,
                CongregateMeals.MealDate, CongregateMeals.MealID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Transportation' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(DailyProgressNotes.MedicalTransportationTrips), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.MedicalTransportation > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'InHomeVisit' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.InHomeVisit), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.InHomeVisit > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'GroupVisit' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.GroupVisit), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.GroupVisit > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID


            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'OffSiteVisit' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.OffSiteVisit), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.OffSiteVisit > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID


            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'FamilySupport' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.FamilySupport), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.FamilySupport > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID


            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'InformationFCSI' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.InformationFCSI), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.InformationFCSI > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID



            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'AssistanceFCSI' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.AssistanceFCSI), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.AssistanceFCSI > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID



            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'LightMealPrep' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.LightMealPrep), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.LightMealPrep > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Escort' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.Escort), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.Escort > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'FoodAssistance' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.FoodAssistance), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.FoodAssistance > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'FoodBox' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.FoodAssistance), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.FoodAssistance > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'HealthPromotions' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.HealthPromotions), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.HealthPromotions > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Information' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.Information), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.Information > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'InPatientVisit' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.InPatientVisit), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.InPatientVisit > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

           UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Interpretation' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.Interpretation), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.Interpretation > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'MedRefillAssistance' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.MedRefillAssistance), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.MedRefillAssistance > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'ReferralService' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.ReferralService), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.ReferralService > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'Telephoning' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM( DailyProgressNotes.TelephoningCount), 0)  AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.Telephoning > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            UNION ALL

            SELECT
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB, 'WritingReading' AS ServiceType,
                DailyProgressNotes.NoteDate AS DateOfService, DailyProgressNotes.DailyProgressNoteID AS ServiceID,
                DailyProgressNotes.ProviderID,
                ISNULL( SUM(1 * DailyProgressNotes.WritingReading), 0) AS TotalUnits
            FROM
                DailyProgressNotes INNER JOIN
                ElderList ON DailyProgressNotes.ElderID = ElderList.ElderListID
            WHERE
                DailyProgressNotes.NoteDate BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateFrom#" /> And
                <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateTo#" /> AND
                DailyProgressNotes.WritingReading > 0 And
                ( ElderList.PriorityID <> 6 OR ElderList.PriorityID <> 8 )
            GROUP BY
                ElderList.ElderListID, ElderList.LastName, ElderList.FirstName, ElderList.DOB,
                DailyProgressNotes.ProviderID, DailyProgressNotes.NoteDate,
                DailyProgressNotes.DailyProgressNoteID

            ORDER BY
                ServiceType

        </cfquery>

        <Cfset retVal.ServicesReport = qryServicesReport />


        <cfreturn qryServicesReport />
    </cffunction>



    <cffunction name="funcQryGetCaseID" returntype="any">
    	<cfargument default="zz" name="ElderLastName" />
        <cfargument default="zz" name="ElderFirstName" />
        <cfargument default="#now()#" name="DateOfBirth" />

        <cfquery datasource="eldersprogram"  name="qryGetCaseID" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
            SELECT
            	TOP 1( [CASE].CA_CLTSPT_ID ) AS CaseID,
                [CASE].CA_PRIOAUTHZ AS PriorAuth
            FROM
            	SCFDBWH.DSAR.dbo.PT INNER JOIN
            	SCFDBWH.DSAR.dbo.[CASE] ON [CASE].CA_PT_ID = PT.PT_ID
            WHERE
            	PT.PT_BIRTH_DT = <cfqueryparam cfsqltype="cf_sql_date" value="#ARGUMENTS.DateOfBirth#" /> AND
                PT.PT_LAST_NAME LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.ElderLastName#" /> AND
                PT.PT_FIRSTNAME LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.ElderFirstName#%" /> AND
            	[CASE].CA_DESC LIKE 'ELDER PROGRAM'
        </cfquery>

        <cfreturn qryGetCaseID />
    </cffunction>



    <!--- Get Ethnicity Report--->
    <cffunction name="funcQryEthnicityDistribution" returntype="any">
        <cfquery datasource="eldersprogram" name="qryEthnicityDistribution">
            SELECT
                Ethnicity.Ethnicity, COUNT(ElderList.ElderListID) AS ElderCount
            FROM
                ElderList INNER JOIN
                Ethnicity ON ElderList.Ethnicity = Ethnicity.EthnicityID
            GROUP BY
                Ethnicity.Ethnicity
        </cfquery>
    	<cfreturn qryEthnicityDistribution />
    </cffunction>
    <!--- Get Ethnicity Report--->



    <!--- Get RegionalCorp Report--->
    <cffunction name="funcQryRegionalCorpDistribution" returntype="any">
        <cfquery datasource="eldersprogram" name="qryRegionalCorpDistribution">
            SELECT
                RegionalCorp.RegionalCorp, COUNT(ElderList.ElderListID) AS ElderCount
            FROM
                ElderList INNER JOIN
                RegionalCorp ON ElderList.RegionalCorp = RegionalCorp.RegionalCorpID
            GROUP BY
                RegionalCorp.RegionalCorp
        </cfquery>
    	<cfreturn qryRegionalCorpDistribution />
    </cffunction>
    <!--- Get RegionalCorp Report--->
    
    
    
    <!--- Get MailingList Report--->
    <cffunction name="funcQryMailingList" returntype="any">
        <cfargument default="-1" name="PriorityID" />
        
        <cfquery datasource="eldersprogram" name="qryPriorityList">
			SELECT     
                ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, ElderList.CellPhone, ElderList.PriorityID, 
                ElderList.MailingAddress, ElderList.MailingAptSpaceNo, ElderList.MailingCity, ElderList.MailingState, ElderList.MailingZip, ElderList.Address, 
                ElderList.City, ElderList.AptSpaceNo, ElderList.State, ElderList.Zip, ElderList.ProviderId, ElderList.EmergencyContactName, 
                ElderList.EmergencyContactHomePhone, ElderList.EmergencyContactCellPhone, Priority.PriorityName
            FROM         
                ElderList INNER JOIN
                Priority ON ElderList.PriorityID = Priority.PriorityID 
			WHERE ElderList.PriorityID = 9
            ORDER BY
                ElderList.LastName                
        </cfquery>
        <cfreturn qryPriorityList />
    </cffunction>
    <!--- Get MailingList Report--->
    
    
    
    <!--- Get MailingList Report--->
    <cffunction name="funcQryNoPriorityList" returntype="any">
        <cfargument default="-1" name="PriorityID" />
        
        <cfquery datasource="eldersprogram" name="qryNoPriorityList">
            SELECT 
                ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, 
                ElderList.CellPhone, ElderList.PriorityID, ElderList.MailingAddress, ElderList.MailingAptSpaceNo, ElderList.MailingCity, ElderList.MailingState, 
                ElderList.MailingZip, ElderList.Address, ElderList.City, ElderList.AptSpaceNo, ElderList.State, ElderList.Zip, ElderList.ProviderID, 
                ElderList.EmergencyContactName, ElderList.EmergencyContactHomePhone, 
                ElderList.EmergencyContactCellPhone
            FROM 
                ElderList 
            WHERE 
                ElderList.PriorityID IS NULL OR ElderList.PriorityID = 0 OR ElderList.PriorityID = -1 OR ElderList.PriorityID = ''
            ORDER BY
                ElderList.LastName                
        </cfquery>
        <cfreturn qryNoPriorityList />
    </cffunction>
    <!--- Get MailingList Report--->
    
    
    
    <!--- Get qryNoProviderList Report--->
    <cffunction name="funcQryNoProviderList" returntype="any">
        <cfargument default="-1" name="PriorityID" />
        
        <cfquery datasource="eldersprogram" name="qryNoProviderList">
            SELECT 
                ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, 
                ElderList.CellPhone, ElderList.PriorityID, ElderList.MailingAddress, ElderList.MailingAptSpaceNo, ElderList.MailingCity, ElderList.MailingState, 
                ElderList.MailingZip, ElderList.Address, ElderList.City, ElderList.AptSpaceNo, ElderList.State, ElderList.Zip, ElderList.ProviderID, 
                ElderList.EmergencyContactName, ElderList.EmergencyContactHomePhone, 
                ElderList.EmergencyContactCellPhone, Priority.PriorityName
            FROM 
                ElderList LEFT JOIN
                Priority ON ElderList.PriorityID = Priority.PriorityID
            WHERE 
                ElderList.ProviderID IS NULL OR ElderList.ProviderID = 0 OR ElderList.ProviderID = -1 OR ElderList.ProviderID = ''
            ORDER BY
                ElderList.LastName                
        </cfquery>
        <cfreturn qryNoProviderList />
    </cffunction>
    <!--- Get qryNoProviderList Report--->    
    
    
    
    
    <!--- Get MailingList Report--->
    <cffunction name="funcQryActivceClientList" returntype="any">
    	<cfargument default="-1" name="PriorityID" />
        
        <cfquery datasource="eldersprogram" name="qryActivceClientList">
			SELECT     
			    ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, ElderList.CellPhone, ElderList.PriorityID, 
				ElderList.MailingAddress, ElderList.MailingAptSpaceNo, ElderList.MailingCity, ElderList.MailingState, ElderList.MailingZip, ElderList.Address, 
				ElderList.City, ElderList.AptSpaceNo, ElderList.State, ElderList.Zip, ElderList.ProviderId, ElderList.EmergencyContactName, 
				ElderList.EmergencyContactHomePhone, ElderList.EmergencyContactCellPhone, Priority.PriorityName
			FROM         
			    ElderList INNER JOIN
			    Priority ON ElderList.PriorityID = Priority.PriorityID
			WHERE     
			    (ElderList.PriorityID <> 8) AND (ElderList.PriorityID <> 6)
			GROUP BY 
			    ElderList.ElderListID, ElderList.FirstName, ElderList.LastName, ElderList.DOB, ElderList.HomePhone, ElderList.CellPhone, ElderList.PriorityID, 
				ElderList.MailingAddress, ElderList.MailingAptSpaceNo, ElderList.MailingCity, ElderList.MailingState, ElderList.MailingZip, ElderList.Address, 
				ElderList.City, ElderList.AptSpaceNo, ElderList.State, ElderList.Zip, ElderList.ProviderId, ElderList.EmergencyContactName, 
				ElderList.EmergencyContactHomePhone, ElderList.EmergencyContactCellPhone, Priority.PriorityName
			ORDER BY ElderList.LastName     	
        </cfquery>
        
    	<cfreturn qryActivceClientList />
    </cffunction>
    <!--- Get MailingList Report--->
    
    
    
    
</cfcomponent>